package com.gome.oa.util;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;

import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

/**
*
* @author: tang li
* @date: 2016年8月13日
* @desc: 
*
*/
public class ExcelUtil {
	
	private static boolean isEmpty(String str) {
		return str == null || str.trim().equals("");
	}
	
	private static int toNumber(String name) {
		int number = 0;
		for (int i = 0; i < name.length(); i++) {
			number = number * 26 + (name.charAt(i) - ('A' - 1));
		}
		return number;
	}

	private static String toName(int number) {
		StringBuilder sb = new StringBuilder();
		while (number-- > 0) {
			sb.append((char) ('A' + (number % 26)));
			number /= 26;
		}
		return sb.reverse().toString();
	}
	
	public static Map<String, List<Map<String, String>>> readExcel(File file) throws Exception {
		Map<String, List<Map<String, String>>> map = new HashMap<String, List<Map<String, String>>>();
		try {
			ZipFile xlsxFile = new ZipFile(file);
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();

			// 先读取sharedStrings.xml这个文件备用
			ZipEntry sharedStringXML = xlsxFile.getEntry("xl/sharedStrings.xml");
			InputStream sharedStringXMLIS = xlsxFile.getInputStream(sharedStringXML);
			Document sharedString = (Document) dbf.newDocumentBuilder().parse(sharedStringXMLIS);
			NodeList str = ((Document) sharedString).getElementsByTagName("t");
			String sharedStrings[] = new String[str.getLength()];
			for (int n = 0; n < str.getLength(); n++) {
				Element element = (Element) str.item(n);
				sharedStrings[n] = element.getTextContent();
			}
			
			// 找到解压文件夹里的workbook.xml,此文件中包含了这张工作表中有几个sheet
			ZipEntry workbookXML = xlsxFile.getEntry("xl/workbook.xml");
			InputStream workbookXMLIS = xlsxFile.getInputStream(workbookXML);
			Document doc = dbf.newDocumentBuilder().parse(workbookXMLIS);
			// 获取一共有几个sheet
			NodeList nl = doc.getElementsByTagName("sheet");

			for (int i = 0; i < nl.getLength(); i++) {
				Element element = (Element) nl.item(i);// 将node转化为element，用来得到每个节点的属性
				List<Map<String, String>> list = new ArrayList<Map<String, String>>();
				map.put(element.getAttribute("name"), list);
				// 接着就要到解压文件夹里找到对应的name值的xml文件，比如在workbook.xml中有<sheet
				// name="Sheet1" sheetId="1" r:id="rId1" /> 节点
				// 那么就可以在解压文件夹里的xl/worksheets下找到sheet1.xml,这个xml文件夹里就是包含的表格的内容
				ZipEntry sheetXML = xlsxFile.getEntry("xl/worksheets/sheet" + element.getAttribute("sheetId").toLowerCase() + ".xml");
				InputStream sheetXMLIS = xlsxFile.getInputStream(sheetXML);
				Document sheetdoc = dbf.newDocumentBuilder().parse(sheetXMLIS);
				NodeList rowdata = sheetdoc.getElementsByTagName("row");
				List<String> columns = new ArrayList<String>();
				for (int rowNo = 0; rowNo < rowdata.getLength(); rowNo++) {
					Map<String, String> rowMap = new LinkedHashMap<String, String>();
					// 得到每个行
					// 行的格式：
					Element row = (Element) rowdata.item(rowNo);
					// 根据行得到每个行中的列
					NodeList columndata = row.getElementsByTagName("c");
					for (int columnNo = 0; columnNo < columndata.getLength(); columnNo++) {
						Element column = (Element) columndata.item(columnNo);
						String content = column.getFirstChild() == null ? "" : column.getFirstChild().getTextContent();
						
						if(sharedStrings.length > 0) {
							NodeList values = column.getElementsByTagName("v");
							Element value = (Element) values.item(0);
							if (column.getAttribute("t") != null & column.getAttribute("t").equals("s")) {
								// 如果是共享字符串则在sharedstring.xml里查找该列的值
								content = sharedStrings[Integer.parseInt(value.getTextContent())];
							} else if(value != null){
								content = value.getTextContent();
							}
						}
						
						if(rowNo == 0) {
							columns.add(content);
						}
						else if(columnNo < columns.size()) {
							rowMap.put(columns.get(columnNo), content);
						}
					}
					if(rowNo > 0) {
						list.add(rowMap);
					}
				}
			}
			xlsxFile.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return map;
	}
	
	/*public static Map<String, List<Map<String, String>>> readExcel(File file) {
		Map<String, List<Map<String, String>>> map = new HashMap<String, List<Map<String, String>>>();
		try {
			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
			int sheetNo = workbook.getNumberOfSheets();
			for (int i = 0; i < sheetNo; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				List<Map<String, String>> list = new ArrayList<Map<String, String>>();
				map.put(sheet.getSheetName(), list);
				
				int rowid = 0;
				while (true) {
					rowid++;
					if (rowid >= sheet.getPhysicalNumberOfRows())
						break;
					Map<String, String> rowMap = new LinkedHashMap<String, String>();
					int columns = sheet.getRow(0).getPhysicalNumberOfCells();
					for (int col = 0; col < columns; col++) {
						Row row = sheet.getRow(rowid); 
						String columnName = sheet.getRow(0).getCell(col).getStringCellValue();
						String content = row.getCell(col).getStringCellValue();
						rowMap.put(isEmpty(columnName) ?  toName(col+1) : columnName, content);
					}
					if(!rowMap.isEmpty()) {
						list.add(rowMap);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}*/

}
